package com.cnhis.cloudhealth.clidoctorweb.test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.cnhis.cloudhealth.commons.commonSql.bo.YaopinKucunBo;
import com.cnhis.cloudhealth.commons.utils.GlobalVariable;

public class JDBCOperation
{
    Connection conn = null;
    public void test() throws SQLException
    {
        synchronized (JDBCOperation.class)
        {
        conn = C3P0ConnentionProvider.getConnection();
        conn.setAutoCommit(false);
        Double updQty = 0d;
        Double refQty = 0d;
        Double sellQty = 0d, quantity = 0d;
        // 需要处理库存的对象
        List dskList = getRefQtyAndSellQty();
        // 找到dpt1表中相同药品不同批次的集合，遍历
        for (int i = 0; i < dskList.size(); i++)
        {
            Map dskObj = (Map)dskList.get(i);
            quantity = GlobalVariable.DoubleIsNull(dskObj.get("quantity"));
            refQty = GlobalVariable.DoubleIsNull(dskObj.get("refqty"));
            sellQty = GlobalVariable.DoubleIsNull(dskObj.get("sellqty"));
            if (i == 0)
            {
                updQty = GlobalVariable.DoubleIsNull(2);
            }
            dskObj.put("updqty", updQty);
            dskObj.put("dsk01", dskObj.get("dsk_id"));
            // 增加预扣数
            if (updQty > 0 && quantity > 0)
            {
                // 如果最小批次够扣库存
                if (updQty <= quantity)
                {
                    select_tmp_DPT1_param1(dskObj);
                    updQty = 0d;
                }
                // 不够，最小批次全扣
                else
                {
                    select_tmp_DPT1_param2(dskObj);
                    updQty = updQty - quantity;
                }
                // ，接着扣下一个批次的
                if (updQty == 0)
                {
                    break;
                }
            }
            
        }
        conn.commit();
        conn.close();
        }
    }
    
    private void select_tmp_DPT1_param2(Map dskObj) throws SQLException
    {
        int i = 0;
        String sql = "update dpt1 set sellqty = refqty, quantity = 0 where bck01 = 72 and bby01 = 6777 and dsk_id = "
            + dskObj.get("dsk01");
        PreparedStatement pstmt = null;
        try
        {
            pstmt = (PreparedStatement)conn.prepareStatement(sql);
            i = pstmt.executeUpdate();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        finally 
        {
            pstmt.close();
        }
    }
    
    private void select_tmp_DPT1_param1(Map dskObj) throws SQLException
    {
        int i = 0;
        String sql = "update dpt1 set sellqty = sellqty + " + dskObj.get("updqty") + ", "
            + "  quantity = case when quantity<0 then 0 else quantity - " + dskObj.get("updqty") + " end"
            + " where bck01 = 72 and bby01 = 6777 and dsk_id = 7228" ;
        PreparedStatement pstmt = null;
        try
        {
            pstmt = (PreparedStatement)conn.prepareStatement(sql);
            i = pstmt.executeUpdate();
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        finally 
        {
            pstmt.close();
        }
    }
    
    private List getRefQtyAndSellQty() throws SQLException
    {
        String sql = "select * from dpt1 where bby01 = 6777 and bck01 = 72";
        PreparedStatement pstmt = null;
        List<Map<String, Object>> dpt1List = new ArrayList<Map<String, Object>>();
        ResultSet rs = null;
        try
        {
            pstmt = (PreparedStatement)conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            int col = rs.getMetaData().getColumnCount();
            System.out.println("============================");
            while (rs.next())
            {
                Map<String, Object> temp = new HashMap<>();
                temp.put("refqty", rs.getDouble("refqty"));
                temp.put("sellqty", rs.getDouble("sellqty"));
                temp.put("quantity", rs.getDouble("quantity"));
                temp.put("bby01", rs.getDouble("bby01"));
                dpt1List.add(temp);
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        finally 
        {
            rs.close();
            pstmt.close();
        }
        return dpt1List;
    }
    
}